# -*- coding: UTF-8 -*-
'''
Created on 2018年3月21日 下午2:28:13

@author: wqp0010@gmail.com
'''

import pymysql

class Employee:
    def __init__(self, firstName, lastName, age, sex, income):
        self.firstName = firstName
        self.lastName = lastName
        self.age = age 
        self.sex = sex 
        self.income = income 
        

class MysqlUtils:
    '''
    classdocs
    '''
    def prtSql(self, sql):
        print("MysqlUtils exe sql : ", sql)

    def __init__(self):
        self.__db = pymysql.connect("192.168.60.54", "root", "admin123", "test")
    
    def insert(self, employee):
        self.__cursor = self.__db.cursor()
        self.__sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
            LAST_NAME, AGE, SEX, INCOME) \
            VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
            (employee.firstName, employee.lastName, employee.age, employee.sex, employee.income)
        self.prtSql(self.__sql)
        try:
            self.__cursor.execute(self.__sql)
            self.__db.commit()
            print("执行成功")
        except:
            self.__db.rollback()
            print("执行失败")
    def update(self, employee):
        self.__cursor = self.__db.cursor()
        self.__sql = "UPDATE EMPLOYEE SET INCOME = %d WHERE FIRST_NAME = '%s' AND LAST_NAME = '%s'" % \
        (employee.income, employee.firstName, employee.lastName)
        self.prtSql(self.__sql)
        try:
            self.__cursor.execute(self.__sql) 
            self.__db.commit()
            print("执行成功")
        except:
            self.__db.rollback()
            print("执行失败")
    def getAll(self):
        self.__cursor = self.__db.cursor()
        self.__sql = "SELECT * FROM EMPLOYEE"
        self.prtSql(self.__sql)
        try:           
            self.__cursor.execute(self.__sql)
            results = self.__cursor.fetchall()
            print("执行成功")
            return results
        except:
            print("执行失败") 
    def delete(self,  employee):
        self.__cursor = self.__db.cursor()
        self.__sql = "DELETE FROM EMPLOYEE WHERE  FIRST_NAME = '%s' AND LAST_NAME = '%s'" % \
        (employee.firstName, employee.lastName)
        self.prtSql(self.__sql)
        try:
            self.__cursor.execute(self.__sql) 
            self.__db.commit()
            print("执行成功")
        except:
            self.__db.rollback()
            print("执行失败")
    def __del__(self):
        self.__db.close()
               
mysqlUtils = MysqlUtils()
#插入
'''
employee =  Employee("Wu", "Qiupeng02", 20, "m", 13000000)
mysqlUtils.insert(employee)
'''

#更新
'''   
employee =  Employee("Wu", "Qiupeng02", 20, "m", 23000000)
mysqlUtils.update(employee)
'''

#获取
'''
list = mysqlUtils.getAll()
for row in list:
    fname = row[1]
    lname = row[2]
    age = row[3]
    sex = row[4]
    income = row[5]
    print ("fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \
        (fname, lname, age, sex, income ))
'''

#删除
employee =  Employee("Wu", "Qiupeng02", 20, "m", 23000000)
mysqlUtils.delete(employee)